var util = require('util');
var async = require('async');
var uuid = require('node-uuid');
var extend = require('extend');

var db = require('./datasource');

var env = process.env.NODE_ENV || 'dev';

var keyIndex = 1;
var keyName = '';

exports.getRecordPaged = function (sql, filter, callback) {
    var page = filter.page;
    var pageSize = filter.pageSize;
    var offset = (page - 1) * pageSize;

    filter.offset = offset;
    filter.rows = pageSize;

    async.series({
        data: function (callback) {
            var sqlPage = concatMysqlPageSql(sql, filter);

            db.exec(sqlPage.clause,sqlPage.param,function(error,res){
                callback(null,res);
            })
        },
        total : function(callback){
            var sqlCount = concatCountSql(sql,filter);

            db.exec(sqlCount.clause,sqlCount.param,function(error,res){
                callback(null,res[0].total);
            })
        }
    },function(err,result){
        result.page = page;
        result.pageSize = pageSize;

        callback(err,result);
    })
}

function concatMysqlPageSql(sql, param) {
    var buffer = "";
    var data = {};

    var filter = param.filter;
    var condition = buildCondition(filter);
    var clauseSql = condition.clause;

    if (clauseSql.length > 0) {
        buffer += "select * from (";
        buffer += sql;
        buffer += ") v where 1 = 1 and ";
        buffer += clauseSql;

        var sortField = param.sort;
        if (sortField != null && sortField.length > 0) {
            var sortStr = "";

            sortField.forEach(function(field){
                sortStr += util.format("%s %s,", field.field, field.dir);
            });

            var sort = util.format(" order by %s", sortStr.substring(0, sortStr.length - 1));
            buffer += sort;
        }
    } else {
        buffer += "select * from (";
        buffer += sql;
        buffer += ") v";

        var sortField =  param.sort;
        if (sortField != null && sortField.length > 0) {
            var sortStr = "";

            sortField.forEach (function(field){
                sortStr += util.format("%s %s,", field.field, field.dir);
            });

            var sort = util.format(" order by %s", sortStr.substring(0, sortStr.length - 1));
            buffer += sort;
        }
    }

    var limit = util.format(" limit %d,%d", param.offset, param.rows);
    buffer += limit;

    data.clause = buffer;
    data.param = extend(condition.param);
    return data;
}

function concatCountSql(sql,param) {
    var data = {};
    var buffer = "";

    var filter = param.filter;
    var condition = buildCondition(filter);
    var clauseSql = condition.clause;

    if (clauseSql.length > 0) {
        buffer += "select count(1) as total from (";
        buffer += sql;
        buffer += ") v where 1 = 1 and ";
        buffer += clauseSql;
    } else {
        buffer += "select count(1) as total from (";
        buffer += sql;
        buffer += ") v";
    }

    data.clause = buffer;
    data.param = extend(condition.param);
    return data;
}


function buildCondition(filter){
    var data = {};

    if(filter == null){
        data.clause = "";
        data.param = {};
        return data;
    }

    var clause = '';
    var subFilters = filter.filters;
    if(subFilters != null && subFilters != undefined && subFilters != ""){
        clause += '(';
        for(var i = 0; i < subFilters.length; i++){
            var f = subFilters[i];

            if (f.logic == null && f.filters == null) {
                var fl = subFilters[i]
                var temp = createSimpleCondition(fl);

                clause += temp.clause;
                data.param = extend(true,data.param,temp.param);

                if (i != subFilters.length - 1) {
                    clause += util.format(" %s ", filter.logic);
                }

            }
            else
            {
                var temp = createCondition(f);

                clause += temp.clause;
                data.param = extend(true,data.param,temp.param);

                if (i < subFilters.length - 1) {
                    clause += util.format(" %s ", filter.logic);
                }
            }
        }
        keyName = '';

        clause += ")";
    }

    data.clause = clause;
    return data;
}

function createCondition(filter) {
    var data = {};

    if (filter == null) {
        return "";
    }

    var filters = filter.filters;
    var clause = "";

    if (filters != null && filters != undefined && filters != "") {
        clause += "(";

        for (var i = 0; i < filters.length; i++) {
            var f = filters[i];
            var temp = createSimpleCondition(f);

            clause += temp.clause;
            data.param = extend(true,data.param,temp.param);

            if (i < (filters.length - 1)) {
                clause += util.format(" %s ", filter.logic);
            }
        }
        keyName = '';

        clause += ")";
    }

    data.clause = clause;
    return data;
}

function createSimpleCondition(filterDesc){
    var clause = '';
    var data = {};
    var param = {};

    if(filterDesc.field != keyName){
        keyIndex = 1;
        keyName = filterDesc.field;
    }else{
        keyIndex ++;
    }

    var key = util.format("%s_%s", filterDesc.field, keyIndex);
    var val = filterDesc.value;
    var value = "'" + val + "'";

    var op = filterDesc.operator;

    if (op == "eq") {
        clause += filterDesc.field + " = :" + key;
        param[key] = val;
    }
    else if (op == "contains"){
        clause += filterDesc.field + " LIKE :" + key;
        param[key] = "%" + val + "%";
    }
    else if (op == "doesnotcontain"){
        clause += filterDesc.field + " NOT LIKE :" + key;
        param[key] = "%" + val + "%";
    }
    else if (op == "startswith"){
        clause += filterDesc.field + " LIKE :"+ key;
        param[key] = val + "%";
    }
    else if (op == "endswith"){
        clause += filterDesc.field + " LIKE :" + key;
        param[key] = "%" + val;
    }
    else if (op == "iscontainedin"){
        clause += filterDesc.field + " IN :" + key;
        param[key] = val;
    }
    else if (op == "gt"){
        clause += filterDesc.field + " > :" + key;
        param[key] = val;
    }
    else if (op == "gte"){
        clause += filterDesc.field + " >= :" + key;
        param[key] = val;
    }
    else if (op == "lt"){
        clause += filterDesc.field + " < :" + key;
        param[key] = val;
    }
    else if (op == "lte"){
        clause += filterDesc.field + " <= :" + key;
        param[key] = val;
    }
    else if (op == "neq"){
        clause += filterDesc.field + " != :" + key;
        param[key] = val;
    }
    else if (op == "in") {
        if (filterDesc.value instanceof Array) {
            var array = filterDesc.value;
            var str = "";

            for (var i = 0; i < array.length; i++) {
                if (i == array.length - 1)
                    str = str + "'" + array[i] + "'";
                else {
                    str = str + "'" + array[i] + "',";
                }
            }

            clause += filterDesc.field + " in (" + str + ")";
        } else if (typeof filterDesc.value == "string") {
            clause += filterDesc.field + " in (" + value + ")";
        }
    } else {
        clause += filterDesc.field + " = :" + key;
        param[key] = val
    }

    data.clause = clause;
    data.param = param;
    return data;
}
